﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ContactManager.Common;
using System.Data.OleDb;

namespace ContactManager
{
    public partial class WorkIncPayFrm : AlSkin.AlForm.AlBaseForm
    {
        DateTime now = DateTime.Now;

        public WorkIncPayFrm()
        {
            InitializeComponent();
        }

        public WorkIncPayFrm(DateTime dt)
        {
            this.now = dt;
            InitializeComponent();
        }

        private void WorkIncPay_Load(object sender, EventArgs e)
        {
            this.labelContactName.Text = Constant.TITLE + GlobleUser.USER_NAME;

            //初始化最近联系人
            this.combRecent.Items.Clear();
            string recentSql = "select top 18 c.c_id,c.c_name,max(t.t_time) from contacters c,traces t where c.c_id = t.t_c_id group by c.c_id,c.c_name order by max(t.t_time) desc";
            DataSet recentDs = AccessHelper.ExecuteQuery(recentSql);
            foreach (DataRow dr in recentDs.Tables[0].Rows)
            {
                ComboBoxItem ci = new ComboBoxItem();
                ci.Text = Convert.ToString(dr["c_name"]);
                ci.Value = dr["c_id"];
                this.combRecent.Items.Add(ci);
            }


            setDate();

        }

        private void setDate()
        {
            //初始化日期
            this.labDate.Text = now.ToString("yyyy年MM月");
            DateTime firstDay = new DateTime(now.Year, now.Month, 1);
            DateTime lastDay = firstDay.AddMonths(1).AddDays(-1);
            int dayNum = DateTime.DaysInMonth(now.Year, now.Month);
            //初始化收入表
            string incSql = "select * from incwork where i_date >=#" + firstDay.ToString("yyyy/MM/dd") + "# and i_date <=#" + lastDay.ToString("yyyy/MM/dd") + "# order by i_date desc";
            DataSet incDs = AccessHelper.ExecuteQuery(incSql);
            this.dataGridViewInc.Rows.Clear();
            foreach (DataRow dr in incDs.Tables[0].Rows)
            {
                int id = Convert.ToInt32(dr[0].ToString());
                DateTime date = Convert.ToDateTime(dr[1].ToString());
                //string week = date.ToString("dddd");
                string explain = dr[2].ToString();
                string contarctNum = dr[3].ToString();
                string getNum = dr[4].ToString();
                DateTime nextDate = Convert.ToDateTime(dr[5].ToString());
                DateTime lastDate = Convert.ToDateTime(dr[6].ToString());
                this.dataGridViewInc.Rows.Add(id, date, explain, contarctNum, getNum, nextDate, lastDate);
            }

            //初始化支出表
            this.dataGridViewPay.Rows.Clear();
            string paySql = "select * from paywork where p_date >=#" + firstDay.ToString("yyyy/MM/dd") + "# and p_date<=#" + lastDay.ToString("yyyy/MM/dd") + "#";
            DataSet payDs = AccessHelper.ExecuteQuery(paySql);
            //填充时间
            for (int dayNumIndex = 0; dayNumIndex < dayNum; dayNumIndex++)
            {
                DateTime dtPay = new DateTime(now.Year, now.Month, 1);
                dtPay = dtPay.AddDays(dayNumIndex);
                string week = dtPay.ToString("dddd");
                this.dataGridViewPay.Rows.Add(0, dtPay.ToString("yyyy-MM-dd"), week);
            }
            //更新grid
            foreach (DataGridViewRow dgr in this.dataGridViewPay.Rows)
            {
                foreach (DataRow dr in payDs.Tables[0].Rows)
                {
                    int id = Convert.ToInt32(dr[0].ToString());
                    DateTime date = Convert.ToDateTime(dr[1].ToString());
                    string explain = dr[2].ToString();
                    double traffic = Convert.ToDouble(dr[3].ToString());
                    double tour = Convert.ToDouble(dr[4].ToString());
                    double serve = Convert.ToDouble(dr[5].ToString());
                    double office = Convert.ToDouble(dr[6].ToString());
                    double meet = Convert.ToDouble(dr[7].ToString());
                    double express = Convert.ToDouble(dr[8].ToString());
                    double phone = Convert.ToDouble(dr[9].ToString());
                    double water = Convert.ToDouble(dr[10].ToString());
                    double rent = Convert.ToDouble(dr[11].ToString());
                    double total = traffic + tour + serve + office + meet + express + phone + water + rent;
                    if (Convert.ToDateTime(dgr.Cells["p_date"].Value) == date)
                    {
                        dgr.Cells["p_id"].Value = id;
                        dgr.Cells["p_explain"].Value = explain;
                        dgr.Cells["p_traffic"].Value = traffic;
                        dgr.Cells["p_tour"].Value = tour;
                        dgr.Cells["p_serve"].Value = serve;
                        dgr.Cells["p_office_supplies"].Value = office;
                        dgr.Cells["p_meet"].Value = meet;
                        dgr.Cells["p_express"].Value = express;
                        dgr.Cells["p_phone"].Value = phone;
                        dgr.Cells["p_water"].Value = water;
                        dgr.Cells["p_rent"].Value = rent;
                        dgr.Cells["p_total"].Value = total;
                        break;
                    }
                }
            }

            //初始化统计
            double inc = 0;//实收金额
            double incDeal = 0;//合同金额
            double pay = 0;//支出

            string iSql = "select sum(i_get_num),sum(i_contract_num) from incwork where i_date >=#" + firstDay.ToString("yyyy/MM/dd") + "# and i_date <=#" + lastDay.ToString("yyyy/MM/dd") + "#";
            DataSet iDs = AccessHelper.ExecuteQuery(iSql);
            if (iDs.Tables[0].Rows[0][0].ToString().Length > 0)
            {
                inc = Convert.ToDouble(iDs.Tables[0].Rows[0][0].ToString());
            }
            if (iDs.Tables[0].Rows[0][1].ToString().Length > 0)
            {
                incDeal = Convert.ToDouble(iDs.Tables[0].Rows[0][1].ToString());
            }

            string pSql = "select sum(p_traffic+p_tour+p_serve+p_office_supplies+p_meet+p_express+p_phone+p_water+p_rent) from paywork where p_date >=#" + firstDay.ToString("yyyy/MM/dd") + "# and p_date<=#" + lastDay.ToString("yyyy/MM/dd") + "#";
            DataSet pDs = AccessHelper.ExecuteQuery(pSql);
            if (pDs.Tables[0].Rows[0][0].ToString().Length > 0)
            {
                pay = Convert.ToDouble(pDs.Tables[0].Rows[0][0].ToString());
            }

            this.labSum.Text = "本月合同收入：" + incDeal.ToString("0.00") + " 实收入金额：" + inc.ToString("0.00") + " 支出：" + pay.ToString("0.00") + " 结余：" + (inc - pay).ToString(".00");


            //算支出合计
            getPaySum();
        }

        private void btnPreviousMonth_Click(object sender, EventArgs e)
        {
            now = now.AddMonths(-1);
            setDate();
        }

        private void btnNow_Click(object sender, EventArgs e)
        {
            now = DateTime.Now;
            setDate();
        }

        private void btnNextMonth_Click(object sender, EventArgs e)
        {
            if (now <= DateTime.Now.AddMonths(-1))
            {
                now = now.AddMonths(1);
                setDate();
            }
        }

        private void btnIncSave_Click(object sender, EventArgs e)
        {
            progressBarMain.Visible = true;
            progressBarMain.Value = 0;
            foreach (DataGridViewRow dr in this.dataGridViewInc.Rows)
            {
                if (dr.Cells["i_income_explain"].Value == null && dr.Cells["i_contract_num"].Value == null && dr.Cells["i_get_num"].Value == null)
                {
                    continue;
                }

                int id = 0;
                if (dr.Cells["i_id"].Value != null)
                { 
                    id = Convert.ToInt32(dr.Cells["i_id"].Value.ToString());
                }

                DateTime date =Convert.ToDateTime(dr.Cells["i_date"].Value);

                string explain = "";
                if (dr.Cells["i_income_explain"].Value != null)
                {
                    explain = dr.Cells["i_income_explain"].Value.ToString();
                }

                string contractNum = "0.00";
                if (dr.Cells["i_contract_num"].Value != null && dr.Cells["i_contract_num"].Value.ToString().Length > 0)
                {
                    contractNum = dr.Cells["i_contract_num"].Value.ToString();
                }

                string getNum = "0.00";
                if (dr.Cells["i_get_num"].Value != null && dr.Cells["i_get_num"].Value.ToString().Length > 0)
                {
                    getNum = dr.Cells["i_get_num"].Value.ToString();
                }

                DateTime nextDate = Convert.ToDateTime(dr.Cells["i_next_date"].Value);

                DateTime lastDate = Convert.ToDateTime(dr.Cells["i_last_date"].Value);

                if (id == 0)
                {
                    string aSql = "insert into incwork(i_date,i_income_explain,i_contract_num,i_get_num,i_next_date,i_last_date) values(#" + date.ToString("yyyy/MM/dd") + "#,?,?,?,#" + nextDate.ToString("yyyy/MM/dd") + "#,#" + lastDate.ToString("yyyy/MM/dd") + "#)";
                    OleDbParameter[] ps = {
                                            new OleDbParameter("@i_income_explain",explain),
                                            new OleDbParameter("@i_contract_num",Math.Round(Convert.ToDouble(contractNum),2)),
                                            new OleDbParameter("@i_get_num",Math.Round(Convert.ToDouble(getNum),2)),
                                            new OleDbParameter("@i_id",id)
                                          };
                    AccessHelper.ExecuteNonQuery(aSql, ps);
                }
                else
                {
                    string uSql = "update incwork set i_date=#" + date.ToString("yyyy/MM/dd") + "#,i_income_explain=?,i_contract_num=?,i_get_num=?,i_next_date=#" + nextDate.ToString("yyyy/MM/dd") + "#,i_last_date=#" + lastDate.ToString("yyyy/MM/dd") + "# where i_id=?";
                    OleDbParameter[] ps = {
                                            new OleDbParameter("@i_income_explain",explain),
                                            new OleDbParameter("@i_contract_num",Math.Round(Convert.ToDouble(contractNum),2)),
                                            new OleDbParameter("@i_get_num",Math.Round(Convert.ToDouble(getNum),2)),
                                            new OleDbParameter("@i_id",id)
                                          };
                    AccessHelper.ExecuteNonQuery(uSql, ps);
                }

                progressBarMain.Value += 100 / this.dataGridViewInc.Rows.Count;
            }
            progressBarMain.Visible = false;
            MessageBox.Show("保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            setDate();
        }

        private void btnIncReset_Click(object sender, EventArgs e)
        {
            this.btnNow_Click(this, null);
        }

        private void dataGridViewInc_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            dataGridViewInc.Rows[e.RowIndex].ErrorText = "";

            if (e.ColumnIndex == 3)
            {
                if (e.FormattedValue.ToString().Length != 0)
                {
                    float newFloat = 0.00f;
                    if (!float.TryParse(e.FormattedValue.ToString(), out newFloat) || newFloat < 0.00)
                    {
                        e.Cancel = true;
                        dataGridViewInc.Rows[e.RowIndex].ErrorText = "数字格式错误，只能为小数点后两位，请重新输入";
                        MessageBox.Show("数字格式错误，只能为小数点后两位，请重新输入");
                        return;
                    }
                }
            }
            else if (e.ColumnIndex == 4)
            {
                if (e.FormattedValue.ToString().Length != 0)
                {
                    float newFloat = 0.00f;
                    if (!float.TryParse(e.FormattedValue.ToString(), out newFloat) || newFloat < 0.00)
                    {
                        e.Cancel = true;
                        dataGridViewInc.Rows[e.RowIndex].ErrorText = "数字格式错误，只能为小数点后两位，请重新输入";
                        MessageBox.Show("数字格式错误，只能为小数点后两位，请重新输入");
                        return;
                    }
                }
                //验证整数
                //int newInteger = 0;
                //if (!int.TryParse(e.FormattedValue.ToString(), out newInteger) || newInteger < 0)
                //{
                //    e.Cancel = true;
                //    dataGridViewInc.Rows[e.RowIndex].ErrorText = "题数格式错误，请重新输入。";
                //    MessageBox.Show("题数格式错误，请重新输入。");
                //    return;
                //}
            }
            else if (e.ColumnIndex == 2)
            {
                if (e.FormattedValue.ToString().Length > 100)
                {
                    e.Cancel = true;
                    dataGridViewInc.Rows[e.RowIndex].ErrorText = "收入说明字数不能大于100字";
                    MessageBox.Show("收入说明字数不能大于100字");
                    return;
                }
            }
        }

        private void btnPayReset_Click(object sender, EventArgs e)
        {
            this.btnNow_Click(this, null);
        }

        private void btnPaySave_Click(object sender, EventArgs e)
        {
            progressBarMain.Visible = true;
            progressBarMain.Value = 0;

            foreach (DataGridViewRow dgr in this.dataGridViewPay.Rows)
            {
                int id = Convert.ToInt32(dgr.Cells["p_id"].Value);
                DateTime date = Convert.ToDateTime(dgr.Cells["p_date"].Value);
                string explain = "";
                if(dgr.Cells["p_explain"].Value != null)
                    explain = dgr.Cells["p_explain"].Value.ToString();

                string traffic = "0";
                if(dgr.Cells["p_traffic"].Value != null)
                    traffic = dgr.Cells["p_traffic"].Value.ToString();

                string tour = "0";
                if(dgr.Cells["p_tour"].Value != null)
                    tour = dgr.Cells["p_tour"].Value.ToString();

                string serve = "0";
                if(dgr.Cells["p_serve"].Value != null)
                    serve = dgr.Cells["p_serve"].Value.ToString();

                string office = "0";
                if(dgr.Cells["p_office_supplies"].Value != null)
                    office = dgr.Cells["p_office_supplies"].Value.ToString();

                string meet = "0";
                if(dgr.Cells["p_meet"].Value != null)
                    meet = dgr.Cells["p_meet"].Value.ToString();

                string express = "0";
                if(dgr.Cells["p_express"].Value != null)
                    express = dgr.Cells["p_express"].Value.ToString();

                string phone = "0";
                if(dgr.Cells["p_phone"].Value != null)
                    phone = dgr.Cells["p_phone"].Value.ToString();

                string water = "0";
                if(dgr.Cells["p_water"].Value != null)
                    water = dgr.Cells["p_water"].Value.ToString();

                string rent = "0";
                if(dgr.Cells["p_rent"].Value != null)
                    rent = dgr.Cells["p_rent"].Value.ToString();

                string total = "0";
                if (dgr.Cells["p_total"].Value != null)
                    total = dgr.Cells["p_total"].Value.ToString();

                if (explain.Length == 0
                    && traffic.Length == 0
                    && tour.Length == 0
                    && serve.Length == 0
                    && office.Length == 0
                    && meet.Length == 0
                    && express.Length == 0
                    && phone.Length == 0
                    && water.Length == 0
                    && rent.Length == 0
                    )
                {
                    continue;
                }

                if (id == 0)
                {
                    string aSql = "insert into paywork(p_date,p_explain,p_traffic,p_tour,p_serve,p_office_supplies,p_meet,p_express,p_phone,p_water,p_rent) values(#" + date.ToString("yyyy/MM/dd") + "#,?,?,?,?,?,?,?,?,?,?)";
                    OleDbParameter[] ps = {
                                            new OleDbParameter("@p_explain",explain),
                                            new OleDbParameter("@p_traffic",traffic),
                                            new OleDbParameter("@p_tour",tour),
                                            new OleDbParameter("@p_serve",serve),
                                            new OleDbParameter("@p_office_supplies",office),
                                            new OleDbParameter("@p_meet",meet),
                                            new OleDbParameter("@p_express",express),
                                            new OleDbParameter("@p_phone",phone),
                                            new OleDbParameter("@p_water",water),
                                            new OleDbParameter("@p_rent",rent)
                                          };
                    AccessHelper.ExecuteNonQuery(aSql, ps);
                }
                else
                {
                    string uSql = "update paywork set p_date=#" + date.ToString("yyyy/MM/dd") + "#,p_explain=?,p_traffic=?,p_tour=?,p_serve=?,p_office_supplies=?,p_meet=?,p_express=?,p_phone=?,p_water=?,p_rent=? where p_id=?";
                    OleDbParameter[] ps = {
                                            new OleDbParameter("@p_explain",explain),
                                            new OleDbParameter("@p_traffic",traffic),
                                            new OleDbParameter("@p_tour",tour),
                                            new OleDbParameter("@p_serve",serve),
                                            new OleDbParameter("@p_office_supplies",office),
                                            new OleDbParameter("@p_meet",meet),
                                            new OleDbParameter("@p_express",express),
                                            new OleDbParameter("@p_phone",phone),
                                            new OleDbParameter("@p_water",water),
                                            new OleDbParameter("@p_rent",rent),
                                            new OleDbParameter("@p_id",id)
                                          };
                    AccessHelper.ExecuteNonQuery(uSql, ps);
                }
                progressBarMain.Value += 100 / this.dataGridViewPay.Rows.Count;
            }
            progressBarMain.Visible = false;
            MessageBox.Show("保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            setDate();
        }

        private void WorkIncPay_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (DialogResult.Yes == MessageBox.Show("您确定要退出吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information))
            {
                HomeFrm.wip = null;
            }
            else
            {
                e.Cancel = true;
            }
        }

        private void exportData_Click(object sender, EventArgs e)
        {
            ExcelUtil.exportWorkInc(this.dataGridViewPay, this.dataGridViewInc, this.labSum.Text, this.progressBarMain);
        }

        private void combRecent_SelectedIndexChanged(object sender, EventArgs e)
        {
            ComboBoxItem recent = (ComboBoxItem)this.combRecent.SelectedItem;
            TracesForm tf = new TracesForm(recent.Value.ToString());
            tf.ShowDialog();
        }

        private void getPaySum()
        { 
            double snjt = 0.0;
            double clf = 0.0;
            double zdf = 0.0;
            double bgyp = 0.0;
            double hwf = 0.0;
            double kdf = 0.0;
            double txf = 0.0;
            double sdf = 0.0;
            double fzf = 0.0;
            double total = 0.0;

            foreach (DataGridViewRow dr in this.dataGridViewPay.Rows)
            {
                snjt += Convert.ToDouble((dr.Cells["p_traffic"].Value == null ? 0.0 : dr.Cells["p_traffic"].Value).ToString());
                clf += Convert.ToDouble((dr.Cells["p_tour"].Value == null ? 0.0 : dr.Cells["p_tour"].Value).ToString());
                zdf += Convert.ToDouble((dr.Cells["p_serve"].Value == null ? 0.0 : dr.Cells["p_serve"].Value).ToString());
                bgyp += Convert.ToDouble((dr.Cells["p_office_supplies"].Value == null ? 0.0 : dr.Cells["p_office_supplies"].Value).ToString());
                hwf += Convert.ToDouble((dr.Cells["p_meet"].Value == null ? 0.0 : dr.Cells["p_meet"].Value).ToString());
                kdf += Convert.ToDouble((dr.Cells["p_express"].Value == null ? 0.0 : dr.Cells["p_express"].Value).ToString());
                txf += Convert.ToDouble((dr.Cells["p_phone"].Value == null ? 0.0 : dr.Cells["p_phone"].Value).ToString());
                sdf += Convert.ToDouble((dr.Cells["p_water"].Value == null ? 0.0 : dr.Cells["p_water"].Value).ToString());
                fzf += Convert.ToDouble((dr.Cells["p_rent"].Value == null ? 0.0 : dr.Cells["p_rent"].Value).ToString());
                total += Convert.ToDouble((dr.Cells["p_total"].Value == null ? 0.0 : dr.Cells["p_total"].Value).ToString());
            }

            this.labSumSnjc.Text = "市内交通：" + Math.Round(snjt, 2).ToString()
                                  + "|差旅：" + Math.Round(clf, 2).ToString()
                                  + "|招待：" + Math.Round(zdf, 2).ToString()
                                  + "|办公用品：" + Math.Round(bgyp, 2).ToString()
                                  + "|会务：" + Math.Round(hwf, 2).ToString()
                                  + "|快递：" + Math.Round(kdf, 2).ToString()
                                  + "|通信：" + Math.Round(txf, 2).ToString()
                                  + "|水电物业：" + Math.Round(sdf, 2).ToString()
                                  + "|水电房租：" + Math.Round(fzf, 2).ToString();
            this.labSumTotal.Text = Math.Round(total, 2).ToString();
        }

        private void dataGridViewPay_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            dataGridViewPay.Rows[e.RowIndex].ErrorText = "";

            if (e.ColumnIndex == 4 
                || e.ColumnIndex == 5
                || e.ColumnIndex == 6
                || e.ColumnIndex == 7
                || e.ColumnIndex == 8
                || e.ColumnIndex == 9
                || e.ColumnIndex == 10
                || e.ColumnIndex == 11
                || e.ColumnIndex == 12
                )
            {
                if (e.FormattedValue.ToString().Length != 0)
                {
                    float newFloat = 0.00f;
                    if (!float.TryParse(e.FormattedValue.ToString(), out newFloat) || newFloat < 0.00)
                    {
                        e.Cancel = true;
                        dataGridViewPay.Rows[e.RowIndex].ErrorText = "数字格式错误，只能为小数点后两位，请重新输入";
                        MessageBox.Show("数字格式错误，只能为小数点后两位，请重新输入");
                        return;
                    }
                }
            }
            else if (e.ColumnIndex == 3)
            {
                if (e.FormattedValue.ToString().Length > 100)
                {
                    e.Cancel = true;
                    dataGridViewPay.Rows[e.RowIndex].ErrorText = "收入说明字数不能大于100字";
                    MessageBox.Show("收入说明字数不能大于100字");
                    return;
                }
            }
        }

        private void btnYear_Click(object sender, EventArgs e)
        {
            if (HomeFrm.ywip == null)
            {
                HomeFrm.ywip = new YearWorkIncPayFrm();
                HomeFrm.ywip.Show();
            }
            else
            {
                HomeFrm.ywip.Activate();
            }
        }

        private void btnAddRow_Click(object sender, EventArgs e)
        {
            DataGridViewRow dr = new DataGridViewRow();

            foreach (DataGridViewColumn c in dataGridViewInc.Columns)
            {
                dr.Cells.Add(c.CellTemplate.Clone() as DataGridViewCell);//给行添加单元格
            }

            dr.Cells[0].Value = 0;
            dr.Cells[1].Value = DateTime.Now;
            dr.Cells[2].Value = "";
            dr.Cells[3].Value = "";
            dr.Cells[4].Value = "";
            dr.Cells[5].Value = DateTime.Now;
            dr.Cells[6].Value = DateTime.Now;

            this.dataGridViewInc.Rows.Insert(0, dr);
            this.dataGridViewInc.Rows[0].Selected = true;
        }

    }
}
